﻿using Publicuse.Entity;
using Publicuse.Interface;
using Publicuse.Util;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Publicuse.Service
{
    public class BaseSqlSugarService : IBaseSqlSugarService
    {
        protected ISqlSugarClient sugarClient;

        public BaseSqlSugarService(ISqlSugarClient sugarClient)
        {
            this.sugarClient = sugarClient;
        }

        /// <summary>
        /// 判断是否含有SQL注入式攻击代码（查询）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static bool ProcessSqlStr(string sql)
        {
            var SqlStr = "exec |insert |delete |update |chr |mid |master |truncate |char |declare |exec|insert|delete|update|chr|mid|master|truncate|char|declare";
            string[] anySqlStr = SqlStr.Split('|');
            bool ReturnValue = false;
            try
            {
                foreach (string ss in anySqlStr)
                {
                    if (sql.IndexOf(ss.stringRT()) >= 0)
                    {
                        ReturnValue = true;
                    }
                }
            }
            catch (Exception ex)
            {
                ReturnValue = true;
            }
            return ReturnValue;
        }

        /// <summary>
        /// 执行任务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /// <param name="istran"></param>
        /// <returns></returns>
        private T DoTask<T>(out string msg, Func<T> action, bool istran = false)
        {
            msg = string.Empty;
            T result = default(T);
            try
            {
                if (istran)
                {
                    this.sugarClient.Ado.BeginTran();  //开启一个事务
                    {
                        result = action.Invoke();
                    }
                    this.sugarClient.Ado.CommitTran();  //提交事务
                }
                else
                {
                    result = action.Invoke();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.InnerException != null)
                {
                    msg += "," + ex.InnerException.Message;
                }
                if (istran)
                {
                    if (this.sugarClient.Ado.Connection.State == ConnectionState.Open)
                    {
                        this.sugarClient.Ado.RollbackTran();//数据回滚
                    }
                }
            }
            return result;
        }

        #region 查询 
        /// <summary>
        /// 查询所有的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> GetAll<T>(out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                return this.sugarClient.Queryable<T>().ToList();
            }, false);
        }

        /// <summary>
        /// 根据SQL查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> QueryBySql<T>(string sql, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    return this.sugarClient.SqlQueryable<T>(sql).ToList();
                }
            }, false);
        }

        /// <summary>
        /// 根据SQL查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    return this.sugarClient.Ado.SqlQuery<T>(sql).ToList();
                }
            }, false);
        }

        public List<T> Query<T>(string sql, object parameter, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    return this.sugarClient.SqlQueryable<T>(sql).ToList();
                }
            }, false);
        }

        /// <summary>
        /// 根据SQL传递参数查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> QueryBySqlPara<T>(string sql, object parameter, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    var data = this.sugarClient.Ado.SqlQuery<T>(sql, parameter).ToList();
                    return data;
                }
            }, false);
        }

        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> QueryByExpression<T>(Expression<Func<T, bool>> expression, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                return this.sugarClient.Queryable<T>().Where(expression).ToList();
            }, false);
        }

        /// <summary>
        /// 通过编号查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T FindById<T>(int id, out string msg) where T : class, new()
        {
            return this.DoTask<T>(out msg, () =>
            {
                return this.sugarClient.Queryable<T>().InSingle(id);
            }, false);
        }

        /// <summary>
        /// 通过编号查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <param name="entity"></param>
        public void QueryInfoByID<T>(int id, out string msg, out T entity) where T : class, new()
        {
            entity = default(T);
            entity = this.DoTask<T>(out msg, () =>
            {
                var data = this.sugarClient.Queryable<T>().InSingle(id);
                return data;
            }, false);

        }

        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T FindByExpression<T>(Expression<Func<T, bool>> expression, out string msg) where T : class, new()
        {
            return this.DoTask<T>(out msg, () =>
            {
                return this.sugarClient.Queryable<T>().Single(expression);
            }, false);
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="parameters"></param>
        /// var parameters = new List<SugarParameter>(){
        /// new SugarParameter("@id",30) };
        /// <returns></returns>
        public DataTable GetDataTable(string sql, out string msg, object parameters)
        {
            return this.DoTask<DataTable>(out msg, () =>
            {
                DataTable dataTable = new DataTable();
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    dataTable = this.sugarClient.Ado.GetDataTable(sql, parameters);
                    return dataTable;
                }
            }, false);
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, object parameters, out string msg)
        {
            return this.DoTask<DataTable>(out msg, () =>
            {
                DataTable dataTable = new DataTable();
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    dataTable = this.sugarClient.Ado.GetDataTable(sql, parameters);
                    return dataTable;
                }
            }, false);
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="parameters"></param>
        /// var parameters = new List<SugarParameter>(){
        /// new SugarParameter("@id",30) };
        /// <returns></returns>
        public DataSet GetDataSet(string sql, out string msg, object parameters)
        {
            return this.DoTask<DataSet>(out msg, () =>
            {
                DataSet dataSet = new DataSet();
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    dataSet = this.sugarClient.Ado.GetDataSetAll(sql, parameters);
                    return dataSet;
                }
            }, false);
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, object parameters, out string msg)
        {
            return this.DoTask<DataSet>(out msg, () =>
            {
                DataSet dataSet = new DataSet();
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                if (ProcessSqlStr(sql))
                {
                    throw new Exception("非法参数异常！");
                }
                else
                {
                    dataSet = this.sugarClient.Ado.GetDataSetAll(sql, parameters);
                    return dataSet;
                }
            }, false);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="expression"></param>
        /// <param name="orderfile"></param>
        /// <param name="orderByType"></param>
        /// <param name="totalCount"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> GetPageList<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> expression, Expression<Func<T, object>> orderfile, OrderByType orderByType, out int totalCount, out string msg) where T : class, new()
        {
            msg = string.Empty;
            totalCount = 0;
            var totalCountb = 0;
            var result = this.DoTask<List<T>>(out msg, () =>
            {
                if (expression == null)
                {
                    expression = (o => true);
                }
                return this.sugarClient.Queryable<T>().Where(expression).OrderBy(orderfile, orderByType).ToPageList(pageIndex, pageSize, ref totalCountb);
            }, false);
            totalCount = totalCountb;
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="msg"></param>
        /// <param name="list"></param>
        /// <param name="totalCount"></param>
        public List<T> GetPageListByProc<T>(CommonQuery query, out string msg, out List<T> list, out long totalCount) where T : class, new()
        {
            msg = string.Empty;
            list = new List<T>();
            totalCount = 0;
            var totalCountb = 0;
            list = this.DoTask<List<T>>(out msg, () =>
            {
                List<SugarParameter> parameters = new List<SugarParameter>();
                var tableName = new SugarParameter("p_table_name", query.tableName);
                var p_files = new SugarParameter("p_files", query.files);
                var pageIndex = new SugarParameter("p_curr_page", query.pageIndex);
                var pageSize = new SugarParameter("p_page_size", query.pageSize);
                var p_order_string = new SugarParameter("p_order_string", "order by " + query.sortFile);
                var p_ordertype_string = new SugarParameter("p_ordertype_string", query.sortType);
                var p_total_where_sql = string.Empty;
                if (query.sqlWhere.Contains("|"))
                {
                    p_total_where_sql = query.sqlWhere.Split('|')[0];
                    query.sqlWhere = query.sqlWhere.Replace("|", " ");
                }
                else
                {
                    p_total_where_sql = query.sqlWhere;
                }
                var p_total_where_string = new SugarParameter("p_total_where_string", p_total_where_sql);
                var p_where_string = new SugarParameter("p_where_string", query.sqlWhere);
                parameters.AddRange(new List<SugarParameter>() { tableName, p_files, pageIndex, pageSize, p_order_string, p_ordertype_string, p_where_string });
                var ReturnV = new SugarParameter("p_total_rows", null, true);
                ReturnV.DbType = System.Data.DbType.Int32;
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                var list = this.sugarClient.Ado.UseStoredProcedure().SqlQuery<T>(query.storedProcedureName, tableName, p_files, pageIndex, pageSize,
                    p_order_string, p_ordertype_string, p_where_string, p_total_where_string, ReturnV);
                if (!string.IsNullOrEmpty(ReturnV.Value.ToString()))
                {
                    totalCountb = (int)ReturnV.Value;
                }
                else
                {
                    totalCountb = 0;
                }
                return list;
            }, false);
            totalCount = totalCountb;
            return list;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="msg"></param>
        /// <param name="list"></param>
        /// <param name="totalCount"></param>
        public List<T> GetPageListBySql<T>(CommonQuery query, out string msg, out List<T> list, out long totalCount) where T : class, new()
        {
            msg = string.Empty;
            list = new List<T>();
            totalCount = 0;
            var totalCountb = 0;
            list = this.DoTask<List<T>>(out msg, () =>
            {
                var sql = $"select {query.files} from {query.tableName} {query.sqlWhere}  order by {query.sortFile} {query.sortType}";
                var list = this.sugarClient.SqlQueryable<T>(sql).ToPageList(query.pageIndex, query.pageSize, ref totalCountb);
                return list;
            }, false);
            totalCount = totalCountb;
            return list;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="msg"></param>
        /// <param name="list"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<T> GetList<T>(CommonQuery query, out string msg, out List<T> list, out long totalCount) where T : class, new()
        {
            msg = string.Empty;
            list = new List<T>();
            totalCount = 0;
            var totalCountb = 0;
            list = this.DoTask<List<T>>(out msg, () =>
            {
                var pageIndex = (query.pageIndex - 1) * query.pageSize;
                var pageSize = query.pageSize;
                var sql = $"select {query.files} from {query.tableName} {query.sqlWhere} order by {query.sortFile} {query.sortType}";
                var list = this.sugarClient.SqlQueryable<T>(sql).ToPageList(query.pageIndex, query.pageSize, ref totalCountb);
                return list;
            }, false);
            totalCount = totalCountb;
            return list;
        }
        #endregion

        #region 增加 
        /// <summary>
        /// 字典插入
        /// 不能写实体中的属性名，必须和数据库保持一致
        /// </summary>
        /// <param name="keyValuePairs"></param>
        /// <param name="msg"></param>
        /// <param name="aliasname"></param>
        /// <returns></returns>
        public int InsertDictionary(Dictionary<string, object> keyValuePairs,out string msg,string aliasname)
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable(keyValuePairs).AS(aliasname).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 匿名对象插入
        /// </summary>
        /// <param name="ob"></param>
        /// <param name="msg"></param>
        /// <param name="aliasname"></param>
        /// <returns></returns>
        public int InsertAnonyobject(object ob, out string msg, string aliasname)
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<Dictionary<string, object>>(ob).AS(aliasname).ExecuteCommand();
            }, true);
        }


        /// <summary>
        /// 插入Guid主键自动赋值
        /// 只要设置为主键，并且C#类型是Guid 只要不传值，会自动赋值 
        /// 注意只能用： ExecuteCommand 方法不能用自增列的方法
        /// [SugarColumn(IsPrimaryKey = true)]
        /// public Guid uId { get; set; }
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int InsertGuid<T>(T t, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 增加实体 
        /// </summary>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T InsertBackModel<T>(T t, out string msg) where T : class, new()
        {
            return this.DoTask<T>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).ExecuteReturnEntity();
            }, true);
        }

        /// <summary>
        /// 增加实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int InserBackId<T>(T t, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).ExecuteReturnIdentity();
            }, true);
        }

        /// <summary>
        /// 增加实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int Insert<T>(T t, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).ExecuteReturnIdentity();
            }, true);
        }

        /// <summary>
        //使用参数化内部分页插入
        //优点：500条以下速度最快，兼容所有类型和emoji，10万以上的大数据也能跑，就是慢些，内部分批量处理过了。
        //缺点：500以上就开始慢了，要加事务才能回滚 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int InsertUseParameter<T>(List<T> t, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).UseParameter().ExecuteCommand();
            }, true);
        }

        /// <summary>
        ///大数据写入（特色功能：大数据处理上比所有框架都要快30%）
        //优点：1000条以上性能无敌手
        //缺点：不支持数据库默认值， API功能简单， 小数据量并发执行不如普通插入，插入数据越大越适合用这个
        //新功能 5.0.44
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="pageSize"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int InsertBulkCopy<T>(List<T> t,int pageSize, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Fastest<T>().PageSize(pageSize).BulkCopy(t);
            }, true);
        }

        /// <summary>
        /// 分页插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="pageSize"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int InsertPage<T>(List<T> t, int pageSize, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                this.sugarClient.Utilities.PageEach(t, 10, pageList =>
                {
                     this.sugarClient.Insertable(pageList).ExecuteCommand();
                    //db.Insertable(List<实体>).UseParameter().ExecuteCommand() 可以试试和上面哪个性能高用哪个
                });
                return 1;
            }, true);
        }

        /// <summary>
        /// 不插入指定字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public int InsertIgnoreColumns<T>(T t, out string msg, params string[] columns) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).IgnoreColumns(columns).ExecuteReturnIdentity();
            }, true);
        }

        /// <summary>
        /// 只插入指定字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public int InsertInsertColumns<T>(T t, out string msg, params string[] columns) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Insertable<T>(t).InsertColumns(columns).ExecuteReturnIdentity();
            }, true);
        }

        /// <summary>
        /// 批量添加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> InsertBatch<T>(List<T> list, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                var result = this.sugarClient.Insertable<T>(list).ExecuteReturnEntity();
                return result as List<T>;
            }, true);
        }

        public List<T> Insert<T>(List<T> list, out string msg) where T : class, new()
        {
            return this.DoTask<List<T>>(out msg, () =>
            {
                var result = this.sugarClient.Insertable<T>(list).ExecuteReturnEntity();
                return result as List<T>;
            }, true);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 根据字典更新
        /// this.sugarClient.Updateable(keyValuePairs).AS(aliasname).WhereColumns("id").ExecuteCommand();
        /// </summary>
        /// <param name="keyValuePairs"></param>
        /// <param name="msg"></param>
        /// <param name="aliasname"></param>
        /// <param name="wherename"></param>
        /// <returns></returns>
        public int UpdateDictionary(Dictionary<string, object> keyValuePairs, out string msg, string aliasname, string wherename)
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(keyValuePairs).AS(aliasname).WhereColumns(wherename).ExecuteCommand();
            }, true);

        }

        /// <summary>
        /// 忽略某一列不更新
        /// this.sugarClient.Updateable(t).IgnoreColumns(it => new{ it.CreateTime}).ExecuteCommand()
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public int UpdateIgnoreColumns<T>(T t, out string msg, Expression<Func<T, object>> columns) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(t).IgnoreColumns(columns).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 只更新某列--按需更新
        /// this.sugarClient.UpdateColumns(it => new { it.Name, it.CreateTime }).ExecuteCommand()
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public int UpdateColumns<T>(T t, out string msg, Expression<Func<T, object>> columns) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(t).UpdateColumns(columns).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 更新忽略null并且忽略默认值 (比如int默认值是0就不更新)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <param name="ignoreAllNullColumns"></param>
        /// <param name="isOffIdentity"></param>
        /// <param name="ignoreAllDefaultValue"></param>
        /// <returns></returns>
        public int UpdateIgnoreColumns<T>(T t, out string msg, bool ignoreAllNullColumns, bool isOffIdentity = false, bool ignoreAllDefaultValue = false) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true, ignoreAllDefaultValue: true).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 条件更新
        /// this.sugarClient.Updateable(studentUp).Where(it => it.Id == 7003783).ExecuteCommand()
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public int UpdateWhere<T>(T t, out string msg, Expression<Func<T, bool>> expression) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(t).Where(expression).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 修改实体
        /// </summary>
        /// <typeparam name="int"></typeparam>
        /// <param name="t"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int Update<T>(T t, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(t).ExecuteCommand();
            }, true);
        }

        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int UpdateBatch<T>(List<T> list, out string msg) where T : class, new()
        {
            return this.DoTask<int>(out msg, () =>
            {
                return this.sugarClient.Updateable(list).ExecuteCommand();
            }, true);
        }

        #endregion

        #region 删除
        /// <summary>
        /// 根据字典集合删除
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="msg"></param>
        /// <param name="aliasname"></param>
        /// <returns></returns>
        public bool DeleteDictionary(List<Dictionary<string, object>> columns, out string msg, string aliasname)
        {
            return this.DoTask<bool>(out msg, () =>
            {
                return this.sugarClient.Deleteable<object>().AS(aliasname).WhereColumns(columns).ExecuteCommand() > 0;
            }, true);
        }

        /// <summary>
        /// 删除指定主键集合数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="msg"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool Delete<T>(out string msg, long[] ids) where T : BaseModel<long>, new()
        {
            return this.DoTask<bool>(out msg, () =>
            {
                return this.sugarClient.Deleteable<T>().In(ids).ExecuteCommand() > 0;
            }, true);
        }

        /// <summary>
        /// 根据ID删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(int id, out string msg) where T : BaseModel<long>, new()
        {
            return this.DoTask<bool>(out msg, () =>
            {
                return this.sugarClient.Deleteable<T>().Where(it => it.Id == id).ExecuteCommand() > 0;
            }, true);
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>

        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(Expression<Func<T, bool>> where, out string msg) where T : class, new()
        {
            return this.DoTask<bool>(out msg, () =>
            {
                return this.sugarClient.Deleteable<T>().Where(where).ExecuteCommand() > 0;
            }, true);
        }

        #endregion

        #region 执行SQL
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="parameters"></param>
        /// var parameters = new List<SugarParameter>(){
        /// new SugarParameter("@id",30) };
        /// <returns></returns>
        public bool ExecuteSql(string sql, out string msg, object parameters)
        {
            return this.DoTask<bool>(out msg, () =>
            {
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                var data = this.sugarClient.Ado.ExecuteCommand(sql, parameters);
                return data > -1;
            }, true);
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool ExecuteSql(string sql, object parameters, out string msg)
        {
            return this.DoTask<bool>(out msg, () =>
            {
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                var data = this.sugarClient.Ado.ExecuteCommand(sql, parameters);
                return data > -1;
            }, true);

        }
        #endregion

        #region 外部执行具体操作
        /// <summary>
        /// 外部执行具体操作
        /// </summary>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /// <param name="istran"></param>
        public void Work(out string msg, Action<ISqlSugarClient> action, bool istran = false)
        {
            msg = string.Empty;
            try
            {
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Closed)
                {
                    this.sugarClient.Ado.Connection.Open();
                }
                if (istran)
                {
                    this.sugarClient.Ado.BeginTran();  //开启一个事务
                    {
                        action.Invoke(this.sugarClient);
                    }
                    this.sugarClient.Ado.CommitTran();  //提交事务
                }
                else
                {
                    action.Invoke(this.sugarClient);
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.InnerException != null)
                {
                    msg += "," + ex.InnerException.Message;
                }
                if (this.sugarClient.Ado.Connection.State == ConnectionState.Open)
                {
                    this.sugarClient.Ado.RollbackTran();//数据回滚
                }
            }
        }
        #endregion
    }
}
